package com.turing.post.organization.impl;

import java.math.BigInteger;
import java.util.List;

import javax.ejb.Local;
import javax.ejb.Remote;
import javax.ejb.Stateless;
import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
import javax.persistence.Query;

import com.turing.post.organization.util.IssueCompany;
import com.turing.post.organization.util.IssueCompanyDao;
import com.turing.post.util.Config;
import com.turing.post.util.GeneralDao;
import com.turing.post.util.PCPSApplicationException;
import com.turing.post.util.PCPSUtil;

/**
 * @author zxp
 * @Dec 17, 2012
 */
@Stateless(mappedName = "organization.impl.IssueCompanyDaoImpl")
@Remote
@Local
public class IssueCompanyDaoImpl implements IssueCompanyDao {
	/**
	 * 实体管理器
	 */
	@PersistenceContext
	protected EntityManager _em;

	@SuppressWarnings("unchecked")
	@Override
	public IssueCompany getIssueCompanyByIssueCompanyName(
			String issueCompanyName) {
		String jpql = "select * from IssueCompany c where c.IssueCompanyName = ?1";
		Query query = _em.createNativeQuery(jpql, IssueCompany.class);
		query.setParameter(1, issueCompanyName);
		List<IssueCompany> list = query.getResultList();
		if (list == null || list.isEmpty() || list.size() < 1) {
			return null;
		}
		return list.get(0);
	}

	@Override
	public IssueCompany getIssueCompanyByIssueCompanyNo(String issueCompanyNo) {
		return _em.find(IssueCompany.class, issueCompanyNo);
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<IssueCompany> getIssueCompanyList(Integer page,
			String issueCompanyName) {
		String sql = "select * from IssueCompany t where 1=1";
		if (!PCPSUtil.isEmpty(issueCompanyName)) {
			sql = sql + " and t.IssueCompanyName like '%" + issueCompanyName
					+ "%'";
		}
		sql = sql + " order by IssueCompanyNo";
		int maxResult = Config.PAGE_SIZE;
		int startPosition = maxResult * page;
		Query query = _em.createNativeQuery(sql, IssueCompany.class)
				.setFirstResult(startPosition).setMaxResults(maxResult);
		return query.getResultList();
	}

	@Override
	public BigInteger getIssueCompanyTotal(String issueCompanyName) {
		String sql = "select nvl(count(*),0) from IssueCompany t where 1=1";
		if (!PCPSUtil.isEmpty(issueCompanyName)) {
			sql = sql + " and t.IssueCompanyName like '%" + issueCompanyName
					+ "%'";
		}
		Query query = _em.createNativeQuery(sql);
		return new BigInteger(query.getSingleResult().toString());
	}

	@Override
	public void insertIssueCompany(IssueCompany issueCompany)
			throws PCPSApplicationException {
		GeneralDao.insert(_em, issueCompany);
	}

	@Override
	public void updateIssueCompany(IssueCompany issueCompany)
			throws PCPSApplicationException {
		GeneralDao.update(_em, issueCompany);
	}

	// @Override
	// public Integer SaleCompanyAndSaleBranchMinLen(String operBranchNo) {
	// String sql = "select nvl(min(length(branchNo)),0) minlength from ((select
	// sc.salecompanyno branchNo,sc.salecompanyname branchName
	// ,sc.issuecompanyno superBranchNo from salecompany sc where
	// sc.issuecompanyno='"
	// + operBranchNo
	// + "')"
	// + "union all(select sb.salebranchno branchNo,sb.salebranchname branchName
	// ,nvl(sb.supersalebranchno,sb.salecompanyno) superBranchNo from salebranch
	// sb where sb.salebranchno like '"
	// + operBranchNo + "%'))";
	// // 超级管理员全部显示
	// if (Config.SUPER_SYSOPUER_BRANCHNO.equals(operBranchNo)) {
	// sql = "select nvl(min(length(branchNo)),0) minlength from ((select
	// sc.salecompanyno branchNo,sc.salecompanyname branchName
	// ,sc.issuecompanyno superBranchNo from salecompany sc)"
	// + "union all(select sb.salebranchno branchNo,sb.salebranchname branchName
	// ,nvl(sb.supersalebranchno,sb.salecompanyno) superBranchNo from salebranch
	// sb)";
	// }
	// Query query = _em.createNativeQuery(sql);
	// return ((BigDecimal) query.getSingleResult()).intValue();
	// }

	// @SuppressWarnings("unchecked")
	// @Override
	// public List<OrganTreeList> getSaleCompanyAndSaleBranchList(
	// String operBranchNo, Integer minLen) {
	// String jpql = "(select sc.salecompanyno branchNo,sc.salecompanyname
	// branchName,sc.issuecompanyno superBranchNo,"
	// + "case when (length(sc.salecompanyno)-"
	// + minLen
	// + ")=0 then 1 "
	// + "when (length(sc.salecompanyno)-"
	// + minLen
	// + ")=2 then 2 "
	// + "when (length(sc.salecompanyno)-"
	// + minLen
	// + ")=4 then 3 "
	// + "when (length(sc.salecompanyno)-"
	// + minLen
	// + ")=6 then 4 "
	// + "when (length(sc.salecompanyno)-"
	// + minLen
	// + ")=8 then 5 "
	// + "when (length(sc.salecompanyno)-"
	// + minLen
	// + ")=10 then 6 "
	// + "end as layer "
	// + "from salecompany sc where sc.issuecompanyno='"
	// + operBranchNo
	// + "')"
	// + "union all(select sb.salebranchno branchNo,sb.salebranchname branchName
	// ,nvl(sb.supersalebranchno,sb.salecompanyno) superBranchNo,"
	// + "case when (length(sb.salebranchno)-"
	// + minLen
	// + ")=0 then 1 "
	// + "when (length(sb.salebranchno)-"
	// + minLen
	// + ")=2 then 2 "
	// + "when (length(sb.salebranchno)-"
	// + minLen
	// + ")=4 then 3 "
	// + "when (length(sb.salebranchno)-"
	// + minLen
	// + ")=6 then 4 "
	// + "when (length(sb.salebranchno)-"
	// + minLen
	// + ")=8 then 5 "
	// + "when (length(sb.salebranchno)-"
	// + minLen
	// + ")=10 then 6 "
	// + "end as layer from salebranch sb where sb.salebranchno like '"
	// + operBranchNo + "%') order by branchNo";
	// // 超级管理员全部显示
	// if (Config.SUPER_SYSOPUER_BRANCHNO.equals(operBranchNo)) {
	// jpql = "(select sc.salecompanyno branchNo,sc.salecompanyname
	// branchName,sc.issuecompanyno superBranchNo,"
	// + "case when (length(sc.salecompanyno)-"
	// + minLen
	// + ")=0 then 1 "
	// + "when (length(sc.salecompanyno)-"
	// + minLen
	// + ")=2 then 2 "
	// + "when (length(sc.salecompanyno)-"
	// + minLen
	// + ")=4 then 3 "
	// + "when (length(sc.salecompanyno)-"
	// + minLen
	// + ")=6 then 4 "
	// + "when (length(sc.salecompanyno)-"
	// + minLen
	// + ")=8 then 5 "
	// + "when (length(sc.salecompanyno)-"
	// + minLen
	// + ")=10 then 6 "
	// + "end as layer "
	// + "from salecompany sc)"
	// + "union all(select sb.salebranchno branchNo,sb.salebranchname branchName
	// ,nvl(sb.supersalebranchno,sb.salecompanyno) superBranchNo,"
	// + "case when (length(sb.salebranchno)-"
	// + minLen
	// + ")=0 then 1 "
	// + "when (length(sb.salebranchno)-"
	// + minLen
	// + ")=2 then 2 "
	// + "when (length(sb.salebranchno)-"
	// + minLen
	// + ")=4 then 3 "
	// + "when (length(sb.salebranchno)-"
	// + minLen
	// + ")=6 then 4 "
	// + "when (length(sb.salebranchno)-"
	// + minLen
	// + ")=8 then 5 "
	// + "when (length(sb.salebranchno)-"
	// + minLen
	// + ")=10 then 6 "
	// + "end as layer from salebranch sb) order by branchNo";
	// }
	// Query query = _em.createNativeQuery(jpql, OrganTreeList.class);
	// List<OrganTreeList> list = query.getResultList();
	// return list;
	// }

	@Override
	public IssueCompany getIssueCompanyForUpdate(String issueCompanyNo) {
		String sql = "select * from issuecompany t where t.issuecompanyno='"
				+ issueCompanyNo + "' for update";
		Query query = _em.createNativeQuery(sql, IssueCompany.class);
		return (IssueCompany) query.getSingleResult();
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<IssueCompany> getDropDownIssueCompany() {
		String sql = "select * from issuecompany t order by IssueCompanyNo";
		Query query = _em.createNativeQuery(sql, IssueCompany.class);
		return query.getResultList();
	}

	// @Override
	// public Integer getIssueBranchAndSaleCompanyAndSaleBranchMinLen(
	// String operBranchNo) {
	// String sql = "select nvl(min(length(branchNo)),0) minlength from ("+
	// "(select t.issuebranchno branchNo,t.issuebranchname
	// branchName,t.superissuebranchno superBranchNo from issuebranch t where
	// t.issuebranchno like '"+operBranchNo+"%')union all"+
	// "(select s.salecompanyno branchNo,s.salecompanyname
	// branchName,s.issuebranchno superBranchNo from SaleCompany s where
	// s.issuecompanyno like '"+operBranchNo+"%')union all"+
	// "(select sb.salebranchno branchNo,sb.salebranchname
	// branchName,nvl(sb.supersalebranchno,sb.saleCompanyNo) superBranchNo from
	// SaleBranch sb where sb.salebranchno like '"+operBranchNo+"%'))";
	// Query query = _em.createNativeQuery(sql);
	// return ((BigDecimal) query.getSingleResult()).intValue();
	// }
	//
	// @Override
	// public List<OrganTreeList>
	// getIssueBranchAndSaleCompanyAndSaleBranchMinLen(
	// String operBranchNo, Integer minLen) {
	// // TODO Auto-generated method stub
	// return null;
	// }
}
